var Msdb = require("../../../db/Msdb.js");
var Mysqldb = require("../../../db/Mysqldb.js");

const log = require('../../../logger.js');
var logger = require('../../../logger.js').logger;
const jwt = require('jsonwebtoken');
const expressJwt = require('express-jwt');
const nodeExcel = require('excel-export');
var Excel = require('exceljs');
var fs = require('fs');
var multer = require('multer');
var UUID = require('uuid');
var request = require("request");
var Redisdb = require("../../../db/Redisdb.js");
var moment = require('moment');

// 使用说明
//查询序列返回 
async function getxl_util(url, type, param, rttype) {
	var db = new Msdb();
	return new Promise(async function (resolve, reject) {
		var rtdata = await db.query(`select next value  for seq_id as xlm`)
		resolve(rtdata.recordsets[0][0].xlm)
	})
}

// 使用说明
// 指定序列名自由返回结果
//查询序列返回 
async function getxl_util_free(seq) {
	var db = new Msdb();
	return new Promise(async function (resolve, reject) {
		var rtdata = await db.query(`select next value  for ${seq} as xlm`)
		resolve(rtdata.recordsets[0][0].xlm)
	})
}

/**
 * 保存操作日志
 * @param {*} inssql  记录的sql
 * @param {*} storagedata   storage里面只有 ygxm 和 ygbh
 * @param {*} bh    批号非必填
 * @param {*} title  业务类型 如 分切报工，包装称重
 * @param {*} type   数据操作类型 新增，删除，更新，关闭，打开，导入，导出等
 * @param {*} table  操作的主要数据表名
 */
async function saveczlog_util(inssql, storagedata, bh, title, type, table) {
	return new Promise(async (resolve, reject) => {
		var db = new Msdb();
		var logid = UUID.v1();
		var clearsqlstr = inssql.replace(/\"/g, "").replace(/\'/g, "")
		var insertlogsql = `insert into shipSRM_czlog (bh,id,ysyj,create_time,czrxm,czrbh,czsj,ywmk,czlx,czzb) values 
      ('${bh}','${logid}','${clearsqlstr}',getdate(),'${storagedata.ygxm}','${storagedata.ygbh}',getdate(),'${title}','${type}','${table}')`
		var insertlog = await db.update(insertlogsql)
		// logger.info("log-sql" + insertlogsql)
		resolve("1")
	})
}


/**
 * 
 * @param {*} ywmk 业务模块
 * @param {*} jdmc  节点名称
 * @param {*} keyword  关键词
 * @param {*} ysyj  原始语句
 * @param {*} czdz  操作动作
 * @param {*} cztable  操作表
 * @param {*} mjph  母卷批号
 * @param {*} fqph  分切批号
 * @param {*} storagedata  
 * @returns 
 */
async function save_qlczslog_util(logobj) {
	return new Promise(async (resolve, reject) => {
		var db = new Msdb(); 
		var {ywmk,jdmc,keyword,ysyj, czdz, cztable,mjph,fqph, storagedata}=logobj
		var insertlogsql = `insert into qb_cpqlczs (id,create_time,czsj,czrbh,czrxm,ywmk,jdmc,keyword,ysyj,
			czdz,cztable,mjph,fqph,cx,bz) values 
      (next value for seq_id,getdate(),getdate(),'${storagedata.ygbh}','${storagedata.ygxm}','${ywmk}',
	  '${jdmc}','${keyword}','${ysyj}','${czdz}','${cztable}','${mjph}','${fqph}',
	  '${storagedata.logincx}','${storagedata.loginbb}')`
	  logger.info("log-sql" + insertlogsql)
		 await db.update(insertlogsql)
		resolve("1")
	})
}


/**
 * 返回流水号 240627001
 * @param {*} seq  序列名
 * @param {*} length 长度 
 * @returns 返回指定序列
 */

async function getxl_util_lsh(seq,length) {
	return new Promise(async (resolve, reject) => {
		let db = new Msdb();
		var rtdata = await db.query(`select next value  for ${seq} as xlm`)
		
	  var currentDate = new Date(); // 获取当前日期  
	  var formattedDate = moment(currentDate).format('YYMMDD');
	  
	  resolve(formattedDate+String(rtdata.recordsets[0][0].xlm).padStart(length, '0'))
	});
  
  }



/**
 * 
 * @param {res直接从路由传过来} res 
 * @param {返回给客户端的数据} data 
 * @param {返回状态，boolean} status 
 * @param {*返回提示语} message 
 */
function res_util(res, data, status, message) {
	const rtdata = {};
	rtdata.resultdata = data;
	rtdata.rtstatus = status;
	rtdata.rtmessage = message;
	res.send(JSON.stringify(rtdata))
}


//修复日期
function repair(i) {
	if (i >= 0 && i <= 9) {
		return "0" + i;
	} else {
		return i;
	}
}

/**
 * 获取当前时间 返回 2018-01-01 00:00:00格式
 */
function getnowdate() {
	var date = new Date();//当前时间
	var year = date.getFullYear() //返回指定日期的年份
	var month = repair(date.getMonth() + 1);//月
	var day = repair(date.getDate());//日
	var hour = repair(date.getHours());//时
	var minute = repair(date.getMinutes());//分
	var second = repair(date.getSeconds());//秒
	//当前时间 
	var curTime = year + "-" + month + "-" + day
		+ " " + hour + ":" + minute + ":" + second;
	    return curTime
}


/**
 * 获取当前时间用于单据号 返回 20180101格式
 */
function getnowdate_djh() {
	var date = new Date();//当前时间
	var year = date.getFullYear() //返回指定日期的年份
	var month = repair(date.getMonth() + 1);//月
	var day = repair(date.getDate());//日
	var hour = repair(date.getHours());//时
	var minute = repair(date.getMinutes());//分
	var second = repair(date.getSeconds());//秒
	//当前时间 
	var curTime = year + '' + month + '' + day;

	return curTime
}



/**
 * 
 * @param {* 表单数据 有特殊数据格式要求{dataIndex：'x',value；'xvalue',ifdb:true}} formdata 
 * @param {*表名} table 
 * @returns 
 */
async function insert_table(formdata, table) {
	return new Promise(async (resolve, reject) => {
		var db = new Msdb();
		var idseq = await getxl_util();
		formdata.id = { dataIndex: "id", value: idseq, ifdb: true, inputType: "none", bt: false, title: "id" }
		var keysql = ``
		var valuesql = ``
		let entries = Object.entries(formdata);
		entries.forEach(([key, item]) => {
			if (item.ifdb) {
				if (item.inputType == "date_item" && item.value === "") {
					keysql = `${keysql}${item.dataIndex},`
					valuesql = `${valuesql}${null},`
				}
				else {
					if (item.value === null) {
						keysql = `${keysql}${item.dataIndex},`
						valuesql = `${valuesql}${null},`
					}
					else {
						keysql = `${keysql}${item.dataIndex},`
						valuesql = `${valuesql}'${item.value}',`
					}
				}
			}
			else;
		
		})
		keysql = `${keysql}create_time,update_time,`
		valuesql = `${valuesql}getdate(),getdate(),`
		keysql = keysql.slice(0, -1);
		valuesql = valuesql.slice(0, -1);
		var sql = `insert into ${table} (${keysql}) values (${valuesql})`
		logger.info("ins sql---" + sql)
		await db.update(sql)
		resolve({ insertsql: sql, data: formdata })
	})
}



/**
 * 会自动更新update_time字段
 * @param {* 表单数据 有特殊数据格式要求{dataIndex：'x',value；'xvalue',ifdb:true}} formdata 
 * @param {*表名} table 
 * @returns 
 */ async function update_table_byid(formdata, table) {
	return new Promise(async (resolve, reject) => {
		if (formdata.id.value === '' || formdata.id.value === "undefined" || formdata.id.value === "null" || formdata.id.value === undefined) { resolve({ updatesql: "", data: formdata, ifok: false }) }
		else {
			var db = new Msdb();
			var updatesql = ``
			let entries = Object.entries(formdata);
			entries.forEach(([key, item]) => {
				if (item.ifdb && item.dataIndex != 'id') {
					item.value == "null"||item.value == "undefined"||item.value === undefined ? item.value = "" : item.value = item.value;
					
					if(item.inputType=='date_item'&&item.value==''){
						try {
							updatesql = `${updatesql}${item.dataIndex}=null,`
						} catch (error) {
								logger.info("update 日期 error")
						}
					}
					else{
						item.value==null?updatesql = `${updatesql}${item.dataIndex}=null,`:
						 updatesql = `${updatesql}${item.dataIndex}='${item.value}',`
					}
				}
				else;
			})
			updatesql = `${updatesql}update_time=getdate(),`
			updatesql = updatesql.slice(0, -1);
			var sql = `update  ${table} set ${updatesql} where id='${formdata.id.value}'`
			logger.info("updatesql",updatesql)
			await db.update(sql)
			resolve({ updatesql: sql, data: formdata, ifok: true })
		}
	})
}



/**
 * 会自动更新update_time字段
 * @param {* 表单数据 有特殊数据格式要求{dataIndex：'x',value；'xvalue',ifdb:true}} formdata 
 * @param {*表名} table 
 * @param {*where条件} wstr 
 * @returns 
 */ async function update_table_wstr(formdata, table, wstr) {
	return new Promise(async (resolve, reject) => {
		if (wstr === '' || wstr === "undefined" || wstr === "null" || wstr === undefined) { resolve({ updatesql: "", data: formdata, ifok: false }) }
		else {
			var db = new Msdb();
			var updatesql = ``
			let entries = Object.entries(formdata);
			entries.forEach(([key, item]) => {
				if (item.ifdb && item.dataIndex != 'id') {
					updatesql = `${updatesql}${item.dataIndex}='${item.value}',`
				}
				else;
			})
			updatesql = `${updatesql}update_time=getdate(),`
			updatesql = updatesql.slice(0, -1);
			var sql = `update  ${table} set ${updatesql} where ${wstr}`
			await db.update(sql)
			resolve({ updatesql: sql, data: formdata, ifok: true })
		}
	})
}


/**
 * 
 * @param {*sql语句} sql 
 * @returns 数据存在返回true，不存在返回false
 */
async function checkbysql(sql) {
	return new Promise(async (resolve, reject) => {
		var db = new Msdb();
		var resdata = await db.query(sql)
		resolve(resdata.recordsets[0].length == 0 ? false : true)
	})
}

/**
 * 
 * @param {*sql语句} sql 
 * @returns 查询方法 返回resdata.recordsets[0]
 * update by wjm 2024-04-24
 */
async function usequery(sql) {
	return new Promise(async (resolve, reject) => {
		var db = new Msdb();
		logger.info(sql)
		var resdata = await db.query(sql)
		resolve(resdata.recordsets[0])
	})
}



/**
 * 预编译查询方法
 * @param {*sql语句} sql 
 * @param {*预编译参数对象} params 
 * @returns 查询方法 返回resdata.recordsets[0]
 * update by wjm 2024-04-24
 * 
 * 使用示例：
 * const sql = "SELECT * FROM users WHERE name = @name AND age > @age";
 * const params = {
 *   name: {
 *     type: "VarChar",
 *     value: "张三"
 *   },
 *   age: {
 *     type: "Int",
 *     value: 18
 *   }
 * };
 * const result = await usequery_pre(sql, params);
 */
async function usequery_pre(sql, params) {
	return new Promise(async (resolve, reject) => {
		try {
			var db = new Msdb();
			
			
			// 将简单参数对象转换为预编译所需的格式
			const preparedParams = {};
			for (const [key, value] of Object.entries(params)) {
				preparedParams[key] = {
					type: typeof value === 'number' ? 'Int' : 'VarChar',
					value: value
				};
			}
			
			// 调用 Msdb 的 querypre 方法
			var resdata = await db.querypre(sql, preparedParams);
			// logger.info("QUERY RESDATA****" + JSON.stringify(resdata.recordsets[0]));
			resolve(resdata.recordsets[0]);
		} catch (error) {
			// logger.error("usequery_pre error: " + error.message);
			reject(error);
		}
	});
}




/**
 * 
 * @param {*sql语句} sql 
 * @returns 查询方法 返回resdata.recordsets[0]
 * update by wjm 2024-04-24
 */
async function usequery_nolog(sql) {
	return new Promise(async (resolve, reject) => {
		var db = new Msdb();
		var resdata = await db.query(sql)
		resolve(resdata.recordsets[0])
	})
}






/**
 * 为formdata数据包增加字段，默认为ifdb:true,inputtype:none,bt:false,title:key
 * @param {*表单数据 有特殊数据格式要求{dataIndex：'x',value；'xvalue',ifdb:true} formdata 
 * @param {{key:value,keyw:valuet}} dataobj 
 */
function addformfield(formdata, dataobj) {
	let entries = Object.entries(dataobj);
	entries.forEach(([key, item]) => {
		formdata[key] = { dataIndex: key, value: item, ifdb: true, inputType: "none", bt: false, title: key }
	})
}

/**
 *   更新formdata filed值，不影响其他属性
 * @param {*表单数据 有特殊数据格式要求{dataIndex：'x',value；'xvalue',ifdb:true} formdata 
* @param {{key:value,keyw:valuet}} dataobj 
*/
function setformfield(formdata, dataobj) {
	let entries = Object.entries(dataobj);
	entries.forEach(([key, item]) => {
		formdata[key].value = item
	})
}


/**
 * 转变recodsets为formdata
 * @param {*recodsets} obj 
 * @returns 
 */


function resutdata_to_formdata(obj) {
	let formdata = {}
	let entries = Object.entries(obj);
	entries.forEach(([key, item]) => {
		formdata[key] = { dataIndex: key, value: item, ifdb: true }
	})
	return formdata
}


/**
 * 转变recodsets为formdata数组
 * @param {*recodsets} arr 
 * @returns 
 */
function resutdata_to_formdata_arr(arr) {
	let rtarr = []
	arr.forEach(obj => {
		let formdata = {}
		let entries = Object.entries(obj);
		entries.forEach(([key, item]) => {
			formdata[key] = { dataIndex: key, value: item, ifdb: true }
		})
		rtarr.push(formdata)
	})
	return rtarr
}


module.exports = {
	getxl_util,
	getnowdate,
	getnowdate_djh,
	saveczlog_util,
	res_util,
	insert_table,
	update_table_wstr,
	update_table_byid,
	resutdata_to_formdata,
	resutdata_to_formdata_arr,
	checkbysql,
	usequery,
	usequery_pre,
	usequery_nolog,
	addformfield,
	setformfield,
	getxl_util_free,
	save_qlczslog_util,
	getxl_util_lsh

};